import json
import numpy as np
import pandas as pd

# 地球半径，单位：km
EARTH_RADIUS = 6371

# 度分秒转度
def dms_to_deg(dms):
    try:
        dms = int(dms)  # 将输入转换为整数类型
        degrees = dms // 10000
        minutes = (dms % 10000) // 100
        seconds = dms % 100
        return degrees + minutes / 60 + seconds / 3600
    except ValueError:
        print(f"无法将 {dms} 转换为整数，请检查数据。")
        return np.nan

# 度转弧度
def deg2rad(deg):
    return deg * np.pi / 180

# 计算两个经纬度点之间的距离（单位：km）
def haversine_distance(lon1, lat1, lon2, lat2):
    lon1, lat1, lon2, lat2 = map(deg2rad, [lon1, lat1, lon2, lat2])
    dlon = lon2 - lon1
    dlat = lat2 - lat1
    a = np.sin(dlat / 2) ** 2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon / 2) ** 2
    c = 2 * np.arcsin(np.sqrt(a))
    return EARTH_RADIUS * c

# 读取 stations.xlsx 文件，获取有效站号
stations_df = pd.read_excel('stations.xlsx')
valid_station_ids = set(stations_df['站号'])

# 读取 data.json 文件
with open('data.json', 'r', encoding='utf-8') as f:
    data = json.load(f)

# 过滤掉不在 stations.xlsx 中的站点
valid_data = [
    item for item in data 
    if item.get('Station_Id_C', '') in valid_station_ids
]

# 过滤降水值为 999999 的数据
valid_data = [item for item in valid_data if float(item['PRE_1h']) != 999999]

# 提取经纬度、站号和站名
coords = np.array([[dms_to_deg(item['Lon']), dms_to_deg(item['Lat'])] for item in valid_data])
station_ids = [item.get('Station_Id_C', '') for item in valid_data]
station_names = [item.get('Station_Name', '') for item in valid_data]

# 存储每个站点 50km 范围内最近站点的距离
min_distances = []

# 遍历每个站点
for i in range(len(coords)):
    current_coord = coords[i]
    other_coords = np.delete(coords, i, axis=0)
    distances = [haversine_distance(current_coord[0], current_coord[1], coord[0], coord[1]) for coord in other_coords]
    # 筛选出 50km 范围内的距离
    valid_distances = [dist for dist in distances if dist <= 50]
    if valid_distances:
        min_dist = min(valid_distances)
        min_distances.append(min_dist)
    else:
        # 如果 50km 内没有其他站点，设为一个较大值（这里用 np.inf）
        min_distances.append(np.inf)

# 对最近距离进行排序，并获取前 20 个站点的索引
sorted_indices = np.argsort(min_distances)[::-1][:20]

# 输出前 20 个站点的站号、站名和最近站点距离
print("站号\t站名\t最近站点距离(km)")
for index in sorted_indices:
    if min_distances[index] == np.inf:
        print(f"{station_ids[index]}\t{station_names[index]}\t无 50km 内站点")
    else:
        print(f"{station_ids[index]}\t{station_names[index]}\t{min_distances[index]:.2f}")